package com.engine.devcom.common.util;

import com.engine.integration.util.StringUtils;
import weaver.conn.RecordSet;
import weaver.docs.category.SecCategoryComInfo;
import weaver.general.Util;

import java.util.ArrayList;
import java.util.List;

public class SqlUtils {


    public static String buildSplitPageSql(String dbType, String sql, int pageSize, int currentPage) {
        String pageSql =
                "select * from (select tmp.*,rownum rn from (" + sql + ") tmp where rownum<=" + pageSize * currentPage + ") where rn>=" + (pageSize * (currentPage - 1) + 1);
        if (dbType.equalsIgnoreCase("sqlserver")) {
            pageSql = "select t.* from (" + sql + ") t where 1=1 and rn>=" + (pageSize * (currentPage - 1) + 1) + " " +
                    "and rn<=" + (pageSize * currentPage);
        } else if (dbType.equalsIgnoreCase("mysql")) {
            pageSql = sql + " limit " + (currentPage - 1) * pageSize + "," + pageSize;
        } else if (dbType.equalsIgnoreCase("postgresql")) {
            pageSql = sql + " limit " + pageSize + " offset " + (currentPage - 1) * pageSize;
        }

        return pageSql;
    }

    /**
     * 模糊查询，拼接字符串
     *
     * @param rs
     * @param fieldName
     * @param fieldValue
     * @return
     */
    public static String concatStr(RecordSet rs, String fieldName, String fieldValue) {
        String convertsql = "";
        //处理兼容性  concat  cast函数
        if (rs.getDBType().equalsIgnoreCase("oracle")) {
            convertsql = " ','||" + fieldName + "||',' like '%,' || '" + fieldValue + "' || ',%' ";
        } else if (rs.getDBType().equalsIgnoreCase("mysql")) {
            convertsql = " CONCAT(','," + fieldName + ",',') like CONCAT('%,','" + fieldValue + "',',%') ";
        } else if (rs.getDBType().equalsIgnoreCase("postgresql")) {
            convertsql = " ','||" + fieldName + "||',' like '%,' || '" + fieldValue + "' || ',%' ";
        } else {
            convertsql =
                    " ','+convert(varchar," + fieldName + ")+ ',' like '%,' + convert(varchar, '" + fieldValue + "') " +
                            "+ ',%' ";
        }

        return convertsql;
    }


    /**
     * sql递归实现：当前节点所有的子的点，包括非直接子节点
     *
     * @param ids
     * @param tableName
     * @return List
     */
    private String getAllChildsSql(String ids, String tableName) {
        String[] idList = ids.split(",");
        RecordSet rs = new RecordSet();
        String idChilds = "";
        String sql = "select id from " + tableName + " start with parentid = ? connect by prior id = parentid";
        if ("sqlserver".equals(rs.getDBType())) {
            sql = "with cte as " +
                    "(" +
                    "select * from " + tableName + " where parentid = ? " +
                    "union all " +
                    "select  a.* from " + tableName + " as a,cte as b where a.parentid = b.id " +
                    ")" +
                    "select id from cte ";
        } else if ("mysql".equals(rs.getDBType())) {//
            sql = " select DISTINCT t.id from (" +
                    " select @id idlist, @lv:=@lv+1 lv," +
                    " (select @id:=group_concat(id separator ',') from " + tableName + " where find_in_set(parentid," +
                    "@id)) sub " +
                    " from " + tableName + " ,(select @id:= ? ,@lv:=0) vars " +
                    " where @id is not null) tl, " + tableName + " t " +
                    " where find_in_set(t.id,tl.idlist) " +
                    " order by lv asc";
        } else if ("postgresql".equals(rs.getDBType())) {
            sql = "with RECURSIVE  cte as " +
                    "(" +
                    "select * from " + tableName + " where parentid = ? " +
                    "union all " +
                    "select  a.* from " + tableName + " as a,cte as b where a.parentid = b.id " +
                    ")" +
                    "select id from cte ";
        }
        if (idList.length > 0) {
            ids = ids + ",";
        }
        for (int i = 0, j = idList.length; i < j; i++) {
            rs.executeQuery(sql, idList[i]);
            while (rs.next()) {
                String idChild = rs.getString("id");
                ids = ids + idChild + ",";
            }
            if (i == j - 1) {
                ids = ids.substring(0, ids.length() - 1);
            }
        }
        return ids;
    }


    /**
     * sql递归实现：当前节点所有的上级节点，包括非直接子节点
     *
     * @param ids
     * @param tableName
     * @return List
     */
    public static List<String> getAllSupsSql(String ids) {
        String[] idList = ids.split(",");
        SecCategoryComInfo comInfo = new SecCategoryComInfo();
        List<String> parentIdList = new ArrayList<>();
        for (String s : idList) {
            String parentId = comInfo.getParentId(s);
            if (StringUtils.isBlank(parentId) || Util.getIntValue(parentId) < 0) {
                return parentIdList;
            }
            parentIdList.add(parentId);
            parentIdList.addAll(getAllSupsSql(parentId));
        }
        return parentIdList;
    }

    /**
     * sql递归实现：当前节点所有的上级节点，包括非直接子节点
     *
     * @param ids
     * @param tableName
     * @return List
     */
    public static String getAllSupsSql(String ids, String tableName) {
        String[] idList = ids.split(",");
        RecordSet rs = new RecordSet();
        String idChilds = "";
        String sql = "select id from " + tableName + " start with id = ? connect by prior parentid = id";
        if ("sqlserver".equals(rs.getDBType())) {
            sql = "with cte as " +
                    "(" +
                    "select * from " + tableName + " where id = ? " +
                    "union all " +
                    "select  a.* from " + tableName + " as a,cte as b where a.id = b.parentid " +
                    ")" +
                    "select id from cte ";
//        } else if ("mysql".equals(rs.getDBType())) {//
//            sql = " select DISTINCT t.id from (" +
//                    " select @id idlist, @lv:=@lv+1 lv," +
//                    " (select @id:=group_concat(id separator ',') from " + tableName + " where find_in_set(parentid," +
//                    "@id)) sub " +
//                    " from " + tableName + " ,(select @id:= ? ,@lv:=0) vars " +
//                    " where @id is not null) tl, " + tableName + " t " +
//                    " where find_in_set(t.id,tl.idlist) " +
//                    " order by lv asc";
        } else if ("postgresql".equals(rs.getDBType())) {
            sql = "with RECURSIVE  cte as " +
                    "(" +
                    "select * from " + tableName + " where id = ? " +
                    "union all " +
                    "select  a.* from " + tableName + " as a,cte as b where a.id = b.parentid " +
                    ")" +
                    "select id from cte ";
        }
        if (idList.length > 0) {
            ids = ids + ",";
        }
        for (int i = 0, j = idList.length; i < j; i++) {
            rs.executeQuery(sql, idList[i]);
            while (rs.next()) {
                String idChild = rs.getString("id");
                ids = ids + idChild + ",";
            }
            if (i == j - 1) {
                ids = ids.substring(0, ids.length() - 1);
            }
        }
        return ids;
    }
}
